package org.firefly.db.util;

import java.io.File;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.RandomStringUtils;
import org.apache.commons.lang3.StringUtils;
import org.firefly.db.dialect.IDialect;
import org.firefly.db.excel.ExcelData;
import org.firefly.db.meta.ColumnMeta;
import org.firefly.db.meta.FK;
import org.firefly.db.meta.IConstraint;
import org.firefly.db.meta.PK;
import org.firefly.db.meta.TableMeta;

public abstract class DBUtils {
	
	/**
	 * exec ip=xx usr=xx pwd=xx db=xx file=xx
	 * @param args
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 * @throws IOException 
	 */
//	public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException {
////	    String usr = "sa";
////	    String pwd = null;
////	    String ip = "172.16.1.1";
////	    String db = null;
////	    String file="c:/";
////	    for (String string : args) {
////            if(string.startsWith("usr=")){
////                usr = StringUtils.trim(string.substring("usr=".length()));
////            }else if(string.startsWith("pwd=")){
////                pwd = StringUtils.trim(string.substring("pwd=".length()));
////            }else if(string.startsWith("db=")){
////                db = StringUtils.trim(string.substring("db=".length()));
////            }else if(string.startsWith("file=")){
////                file = StringUtils.trim(string.substring("file=".length()));
////            }else if(string.startsWith("ip=")){
////                ip = StringUtils.trim(string.substring("ip=".length()));
////            }
////        }
////	    
//	    Class.forName("net.sourceforge.jtds.jdbc.Driver");
//        Connection srcConn = java.sql.DriverManager.getConnection("jdbc:jtds:sqlserver://172.16.1.1:1433/xx", usr, pwd);
////        Connection srcConn = java.sql.DriverManager.getConnection("jdbc:jtds:sqlserver://"+ip+":1433/"+db, usr, pwd);
//        try {
//            DBInfo srcInfo = new DBInfo(srcConn, null, DBName.SqlServer2005);
//            File sqlFile = new File("c:/");
//            DBUtils.dbConvertA2SqlFile(srcInfo, sqlFile);
//        } finally{
//            srcConn.close();
////          tarConn.close();
//        }
//	}
    
    
//	public static void main(String[] args) throws ClassNotFoundException, SQLException {
//		Class.forName("oracle.jdbc.driver.OracleDriver");
//		Class.forName("com.mysql.jdbc.Driver");
//		Class.forName("net.sourceforge.jtds.jdbc.Driver");
//		Class.forName("org.apache.derby.jdbc.ClientDriver");
//		Connection srcConn = java.sql.DriverManager.getConnection("jdbc:derby://localhost:1527/eadpPro", "firefly", "123456");
//		Connection srcConn = java.sql.DriverManager.getConnection("dbc:oracle:thin:@172.16.236.128:1521:orcl", "eadpDemo", "eplugger");
//		Connection tarConn = java.sql.DriverManager.getConnection("jdbc:jtds:sqlserver://172.16.236.128:1433/RDCWV1", "sa", "eplugger");
//		Connection tarConn = java.sql.DriverManager.getConnection("jdbc:jtds:sqlserver://172.16.1.97:1433/RDASSESS_DEMO", "sa", "eplugger");
//		Connection tarConn = java.sql.DriverManager.getConnection("jdbc:derby://localhost:1527/casv1_1;create=true", "firefly", "123456");
//		try {
//			IDialect srcDia = new OracleDialect(srcConn,"EADPDEMO");
////			IDialect tarDia = new OracleDialect(tarConn,"PRO30015");
//			IDialect tarDia = new MSSQL2005Dialect(tarConn,null);
//			dbConvertA2B(srcConn, tarConn, srcDia, tarDia);
//		} finally{
//			srcConn.close();
//			tarConn.close();
//		}
		
//		System.out.println("========转回=======");
//		Connection srcConn1 = java.sql.DriverManager.getConnection("dbc:oracle:thin:@172.16.1.71:1521:orcl", "pro30015", "eplugger");
//		Connection tarConn1 = java.sql.DriverManager.getConnection("jdbc:derby://localhost:1527/eadpPro2;create=true", "firefly", "123456");
//		try {
//			IDialect srcDia1 = new OracleDialect(srcConn1,"PRO30015");
//			IDialect tarDia1 = new DerbyDialect(tarConn1,"FIREFLY");
//			dbConvertA2B(srcConn1, tarConn1, srcDia1, tarDia1);
//		} finally{
//			srcConn1.close();
//			tarConn1.close();
//		}
//	}
	
	/**
	 * 导出某表的insert语句
	 * @param srcConn
	 * @param srcDia
	 * @param osw
	 * @param tables
	 * @throws SQLException
	 * @throws IOException
	 */
	public static void expertInsertSQL(DBInfo srcInfo,IDialect tarDia,OutputStreamWriter osw,String[] tables,String where,boolean needCloseOS) throws SQLException, IOException{
		//准备：获得要创建的表元数据
		List<TableMeta> tMls = srcInfo.getMeta().getTableMetas(true,true,false,tables);
		for (TableMeta tm : tMls) {
			//1.获取源数据
			List<Map<String, Object>> datas = getTableData(srcInfo.getConn(), tm, srcInfo.getDialect(),where);
			//2.向目标库插入数据
			List<String> insertSQLs = buildInsertSQL(datas, tm, tarDia);
			osw.write("\n\n--表["+tm.getName()+"]的数据如下:\n");
			for (int j = 0; j < insertSQLs.size(); j++) {
				osw.write(insertSQLs.get(j)+";\n");
			}
		}
		if(needCloseOS){
			osw.flush();
			osw.close();
		}
	}

	/**
     * 导出某表的update语句
     * @param srcConn
     * @param srcDia
     * @param osw
     * @param tables
     * @throws SQLException
     * @throws IOException
     */
    public static void expertUpdateSQL(DBInfo srcInfo,IDialect tarDia,OutputStreamWriter osw,String[] tables,String where,boolean needCloseOS) throws SQLException, IOException{
        //准备：获得要创建的表元数据
        List<TableMeta> tMls = srcInfo.getMeta().getTableMetas(true,true,false,tables);
        for (TableMeta tm : tMls) {
            //1.获取源数据
            List<Map<String, Object>> datas = getTableData(srcInfo.getConn(), tm, srcInfo.getDialect(),where);
            //2.向目标库插入数据
            List<String> updateSQLs = buildUpdateSQL(datas, tm, tarDia);
            osw.write("\n\n--表["+tm.getName()+"]的数据如下:\n");
            for (int j = 0; j < updateSQLs.size(); j++) {
                osw.write(updateSQLs.get(j)+";\n");
            }
        }
        if(needCloseOS){
            osw.flush();
            osw.close();
        }
    }
	
	/**
	 * 导出外键索引
	 * @param srcConn
	 * @param srcDia
	 * @param tarDia
	 * @param osw
	 * @param tables
	 * @param needCloseOS
	 * @throws SQLException
	 * @throws IOException
	 */
	public static void expertFKIndexSQL(DBInfo dbInfo,OutputStreamWriter osw,String[] tables,boolean needCloseOS) throws SQLException, IOException{
		//准备：获得要创建的表元数据
		List<TableMeta> tMls = dbInfo.getMeta().getTableMetas(false,false,true,tables);
		for (TableMeta tm : tMls) {
			for (IConstraint c : tm.getConstraintLs()) {
				if(FK.class.isInstance(c)){
					FK fkc = (FK)c;
					//约束名 长度不能超出15位
					StringBuffer indexName = new StringBuffer("I_").append(fkc.getFkTableName()).append("_").append(fkc.getFkTableColumn());
					StringBuffer index = new StringBuffer("create index ").append(indexName)
							.append(" on ").append(fkc.getFkTableName()).append("(").append(fkc.getFkTableColumn()).append(");\n");
					osw.write(index.toString());
				}
			}
		}
		if(needCloseOS){
			osw.flush();
			osw.close();
		}
	}
	
	/**
	 * 数据表转换
	 * @param srcConn 数据源库连接
	 * @param tarConn 目标库连接
	 * @param srcDia 源库方言
	 * @param tarDia 目标库方言
	 * @throws SQLException 
	 */
	public static void tableConvertA2B(DBInfo srcInfo,DBInfo tarInfo,String[] tables) throws SQLException{
		//准备：获得要创建的表元数据
		List<TableMeta> tMls = srcInfo.getMeta().getTableMetas(true,true,true,tables);
		System.out.println("[准备信息]：需要处理"+tMls.size()+"个表");
		System.out.println("[步骤1]：建表==========start");
		for (int i = 0; i < tMls.size(); i++) {
			TableMeta tm = tMls.get(i);
			System.out.println("正在建表["+tm.getName()+"],"+(i+1)+"/"+tMls.size());
			//1.先在目标库中删除
//			List<TableMeta> hasTable = tarDia.getTableMetas(false, false, false, new String[]{tm.getName()});
			List<String> dropSqls = buildDropTableSQL(tm, tarInfo.getDialect());
			for (String dropSql : dropSqls) {
				try {
					tarInfo.getConn().createStatement().execute(dropSql);
				} catch (Exception e) {
					System.err.println("删除表错误，[删表语句]："+dropSql);
					//无法删除的原因
				}
			}
			//2.在目标库创建表
			String createTableSql = buildCreateTableSQL(tm, tarInfo.getDialect());
			try {
				tarInfo.getConn().createStatement().execute(createTableSql);
			} catch (SQLException e) {
				System.err.println("[建表语句]："+createTableSql);
				e.printStackTrace();
			}
			
			
//			//1.获取源数据
//			List<Map<String, Object>> datas = getTableData(srcConn, tm, srcDia,where);
//			//2.向目标库插入数据
//			List<String> insertSQLs = buildInsertSQL(datas, tm, tarDia);
//			osw.write("\n\n--表["+tm.getName()+"]的数据如下:\n");
//			for (int j = 0; j < insertSQLs.size(); j++) {
//				osw.write(insertSQLs.get(j)+";\n");
//			}
		}
		System.out.println("[步骤1]：建表==========end");
		System.out.println("[步骤2]：转数据==========start");
		for (int i = 0; i < tMls.size(); i++) {
			TableMeta tm = tMls.get(i);
			//4.获取源数据
			List<Map<String, Object>> datas = getTableData(srcInfo.getConn(), tm, srcInfo.getDialect(),null);
			System.out.println("正在转移表["+tm.getName()+"]的数据（共计"+datas.size()+"条）,"+(i+1)+"/"+tMls.size());
			//5.向目标库插入数据
			List<String> insertSQLs = buildInsertSQL(datas, tm, tarInfo.getDialect());
			for (int j = 0; j < insertSQLs.size(); j++) {
				try {
					tarInfo.getConn().createStatement().execute(insertSQLs.get(j));
				} catch (SQLException e) {
					System.out.println("[插入语句]："+insertSQLs.get(j));
					e.printStackTrace();
					throw e;
				}
			}
		}		
		System.out.println("[步骤2]：转数据==========end");
	}


    /**
     * 导入excel 到数据库
     * @author 尹智军
     * @param tarInfo
     * @param excel
     * @param tableName
     * @param titleColumn
     * 2016年12月23日
     */
    public static void impExcel2DB(DBInfo tarInfo, File excel, String tableName, Map<String,String> titleColumn){
        ExcelData ed = new ExcelData(tarInfo, tableName);
        try {
            ExcelUtils.processImortData(excel, ed, titleColumn);
            String createSQL = ed.makeCreateSQL();
            System.out.println("建表：" + createSQL);
            tarInfo.getConn().createStatement().execute(createSQL);
        } catch (Exception e) {
            System.err.println("建表错误，e:" + e.getMessage());
            e.printStackTrace();
            return;
        }
        int rowCount = ed.getRowCount();
        System.out.println("要数据的行数为：" + rowCount);
        String insertTempletSQL = ed.makeInsertTempletSQL();
        System.out.println("插入语句的模板：" + insertTempletSQL);
        for (int i = 0; i < rowCount; i++) {
            Object[] rowDataParam = ed.makeInsertParams(i);
            if(rowDataParam != null){
                PreparedStatement ps = null;
                try {
                    ps = tarInfo.getConn().prepareStatement(insertTempletSQL);
                    for (int j = 0; j < rowDataParam.length; j++) {
                        ps.setObject(j+1, rowDataParam[j]);
                    }
                    ps.executeUpdate();
                } catch (Exception e) {
                    System.err.println("插入数据错误,行号："+i+" , e:" + e.getMessage());
                    e.printStackTrace();
                }finally{
                    if(ps!=null){
                        try {
                            ps.close();
                        } catch (SQLException e) {
                            e.printStackTrace();
                        }
                    }
                }
            }
        }
    }
	
	/**
	 * 数据库转换
	 * @param srcConn 数据源库连接
	 * @param tarConn 目标库连接
	 * @param srcDia 源库方言
	 * @param tarDia 目标库方言
	 * @throws SQLException 
	 */
	public static void dbConvertA2B(DBInfo srcInfo,DBInfo tarInfo,boolean targetIsBlank) throws SQLException{
		//准备：获得要创建的表元数据
		List<TableMeta> tMls = srcInfo.getMeta().getTableMetas(true,true,true,null);
		System.out.println("[准备信息]：需要处理"+tMls.size()+"个表");
		System.out.println("[步骤1]：建表==========start");
		for (int i = 0; i < tMls.size(); i++) {
			TableMeta tm = tMls.get(i);
			System.out.println("正在建表["+tm.getName()+"],"+(i+1)+"/"+tMls.size());
			//1.先在目标库中删除
//			List<TableMeta> hasTable = tarDia.getTableMetas(false, false, false, new String[]{tm.getName()});
			if(!targetIsBlank){//没有就不删
				List<String> dropSqls = buildDropTableSQL(tm, tarInfo.getDialect());
				for (String dropSql : dropSqls) {
					try {
						tarInfo.getConn().createStatement().execute(dropSql);
					} catch (Exception e) {
						System.err.println("删除表错误，[删表语句]："+dropSql);
						//无法删除的原因
					}
				}
			}
			//2.在目标库创建表
			String createTableSql = buildCreateTableSQL(tm, tarInfo.getDialect());
			try(Statement s =tarInfo.getConn().createStatement()) {
				s.execute(createTableSql);
			} catch (SQLException e) {
				System.err.println("[建表语句]："+createTableSql);
				e.printStackTrace();
			}
		}
		System.out.println("[步骤1]：建表==========end");
		System.out.println("[步骤2]：转数据==========start");
		for (int i = 0; i < tMls.size(); i++) {
			TableMeta tm = tMls.get(i);
			//4.获取源数据
			List<Map<String, Object>> datas = getTableData(srcInfo.getConn(), tm, srcInfo.getDialect(),null);
			System.out.println("正在转移表["+tm.getName()+"]的数据（共计"+datas.size()+"条）,"+(i+1)+"/"+tMls.size());
			//5.向目标库插入数据
			List<String> insertSQLs = buildInsertSQL(datas, tm, tarInfo.getDialect());
			for (int j = 0; j < insertSQLs.size(); j++) {
			    try(Statement s =tarInfo.getConn().createStatement()) {
					s.execute(insertSQLs.get(j));
				} catch (SQLException e) {
					System.out.println("[插入语句]："+insertSQLs.get(j));
					e.printStackTrace();
					throw e;
				}
			}
		}		
		System.out.println("[步骤2]：转数据==========end");
		System.out.println("[步骤3]：补约束==========start");
		for (int i = 0; i < tMls.size(); i++) {
			TableMeta tm = tMls.get(i);
			System.out.println("正在补表["+tm.getName()+"]的约束,"+(i+1)+"/"+tMls.size());
			//3.构建约束信息
			for (IConstraint c : tm.getConstraintLs()) {
				if(PK.class.isInstance(c)){
					// 3.1 构建主键约束
					String pkSql = buildAddPKSQL((PK)c, tarInfo.getDialect());
					System.out.println(pkSql);
					try {
						tarInfo.getConn().createStatement().execute(pkSql);
					} catch (SQLException e) {
						System.err.println("[约束-主键语句]："+pkSql);
						e.printStackTrace();
						throw e;
					}
				}else if(FK.class.isInstance(c)){
					// 3.2 构建外键约束
					String fkSql = buildAddFKSQL((FK)c, tarInfo.getDialect());
					System.out.println(fkSql);
					try {
						tarInfo.getConn().createStatement().execute(fkSql);
					} catch (SQLException e) {
						System.err.println("[约束-外键语句]："+fkSql);
						e.printStackTrace();
					//	throw e;
					}
				}
			}
		}
		System.out.println("[步骤3]：补约束==========end");
		System.out.println("[结束信息]：全部处理完毕！");
	}
	
	public static void dbConvertA2SqlFile(DBInfo srcInfo,File f) throws SQLException, IOException{
		//准备：获得要创建的表元数据
		List<TableMeta> tMls = srcInfo.getMeta().getTableMetas(true,true,true,null);
		System.out.println("[准备信息]：需要处理"+tMls.size()+"个表");
		System.out.println("[步骤1]：建表==========start");
		for (int i = 0; i < tMls.size(); i++) {
			TableMeta tm = tMls.get(i);
			System.out.println("正在建表["+tm.getName()+"],"+(i+1)+"/"+tMls.size());
			//1.在目标库创建表
			String createTableSql = buildCreateTableSQL(tm, srcInfo.getDialect());
			FileUtils.writeStringToFile(f, createTableSql+";\n",true);
		}
		System.out.println("[步骤1]：建表==========end");
		System.out.println("[步骤2]：转数据==========start");
		for (int i = 0; i < tMls.size(); i++) {
			TableMeta tm = tMls.get(i);
			//4.获取源数据
			List<Map<String, Object>> datas = getTableData(srcInfo.getConn(), tm, srcInfo.getDialect(),null);
			System.out.println("正在转移表["+tm.getName()+"]的数据（共计"+datas.size()+"条）,"+(i+1)+"/"+tMls.size());
			//5.向目标库插入数据
			List<String> insertSQLs = buildInsertSQL(datas, tm, srcInfo.getDialect());
			FileUtils.writeLines(f, insertSQLs,";\n", true);
		}		
		System.out.println("[步骤2]：转数据==========end");
		System.out.println("[步骤3]：补约束==========start");
		for (int i = 0; i < tMls.size(); i++) {
			TableMeta tm = tMls.get(i);
			System.out.println("正在补表["+tm.getName()+"]的约束,"+(i+1)+"/"+tMls.size());
			//3.构建约束信息
			for (IConstraint c : tm.getConstraintLs()) {
				if(PK.class.isInstance(c)){
					// 3.1 构建主键约束
					String pkSql = buildAddPKSQL((PK)c, srcInfo.getDialect());
					FileUtils.writeStringToFile(f, pkSql+";\n",true);
				}else if(FK.class.isInstance(c)){
					// 3.2 构建外键约束
					String fkSql = buildAddFKSQL((FK)c, srcInfo.getDialect());
					FileUtils.writeStringToFile(f, fkSql+";\n",true);
				}
			}
		}
		System.out.println("[步骤3]：补约束==========end");
		System.out.println("[结束信息]：全部处理完毕！");
	}
	
	
	/**
	 * 构建清除表数据SQL
	 * @param tm 表结构
	 * @param tarDia 目标库方言
	 * @return
	 */
	public static String buildDeleteTableDataSQL(TableMeta tm,IDialect tarDia,String where){
		StringBuffer deleteSQL = new StringBuffer("delete table ");
		deleteSQL.append(tarDia.getNamingChar()).append(tm.getName()).append(tarDia.getNamingChar());
		if(where != null){
			deleteSQL.append(where);
		}
		return deleteSQL.toString();
	}

	/**
	 * 构建清空表的sql
	 * @author firefly
	 * @scene 2018年1月16日
	 * @param srcInfo
	 * @param tarDia
	 * @param tables
	 * @return
	 * @throws SQLException
	 */
	public static String deleteTableDataSQL(DBInfo srcInfo,IDialect tarDia,String[] tables) throws SQLException{
        StringBuffer createSQL = new StringBuffer();
        List<TableMeta> tMls = srcInfo.getMeta().getTableMetas(false, true, true, tables);
        for (TableMeta tableMeta : tMls) {
            createSQL.append(buildDeleteTableDataSQL(tableMeta,tarDia,null));
        }
        return createSQL.toString();
    }
	
	
	/**
	 * 构建删除表的sql
	 * @author firefly
	 * @scene 2018年1月16日
	 * @param srcInfo
	 * @param tarDia
	 * @param tables
	 * @return
	 * @throws SQLException
	 */
	public static List<String> dropTableSQL(DBInfo srcInfo,IDialect tarDia,String[] tables) throws SQLException{
		List<String> dropSQL = new ArrayList<String>();
		List<TableMeta> tMls = srcInfo.getMeta().getTableMetas(false, true, true, tables);
		for (TableMeta tableMeta : tMls) {
			dropSQL.addAll(buildDropTableSQL(tableMeta,tarDia));
		}
		return dropSQL;
	}
	
	/**
	 * 构建生成表的sql
	 * @author firefly
	 * @scene 2018年1月16日
	 * @param srcInfo
	 * @param tarDia
	 * @param tables
	 * @return
	 * @throws SQLException
	 */
	public static String createTableSQL(DBInfo srcInfo,IDialect tarDia,String[] tables) throws SQLException{
		StringBuffer createSQL = new StringBuffer();
		List<TableMeta> tMls = srcInfo.getMeta().getTableMetas(true, true, true, tables);
		for (TableMeta tableMeta : tMls) {
			createSQL.append(buildCreateTableSQL(tableMeta,tarDia));
		}
		return createSQL.toString();
	}
	
	/**
	 * 构建删除表SQL
	 * @param tm 表结构
	 * @param tarDia 目标库方言
	 * @return
	 */
	private static List<String> buildDropTableSQL(TableMeta tm,IDialect tarDia){
		//创建删表约束语句
		List<String> ls = new ArrayList<String>();
		ls.addAll(buildDropConstraintSQL(tm,tarDia));
		//创建删表语句
		StringBuffer dropSQL = new StringBuffer("drop table ");
		dropSQL.append(tarDia.getNamingChar()).append(tm.getName()).append(tarDia.getNamingChar());
		ls.add(dropSQL.toString());
		return ls;
	}
	
	/**
	 * 构建删除约束的语句
	 * @param tm
	 * @return
	 */
	private static List<String> buildDropConstraintSQL(TableMeta tm,IDialect tarDia){
		List<String> dropConstraintSQLs = new ArrayList<String>();
		if(tm.getConstraintLs().size()>0){
			for (IConstraint c : tm.getConstraintLs()) {
				if(c instanceof FK){
				StringBuffer dropConstraintSQL = new StringBuffer("alter table ");
				dropConstraintSQL.append(tarDia.getNamingChar()).append(tm.getName()).append(tarDia.getNamingChar()).append(" drop constraint ").append(c.getName());
				dropConstraintSQLs.add(dropConstraintSQL.toString());
				}
			}
		}
		return dropConstraintSQLs;
	}
	 
	static char[] s = new char[]{'0','1','2','3','4','5','6','7','8','9','A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'};

	/**
	 * 构建添加主键SQL
	 * @param p 主键信息
	 * @param tarDia 目标库方言
	 * @return
	 */
	//alter table CFG_SUBMODULE add constraint PK__cfg_submodule__4E498009 primary key(ID);
	private static String buildAddPKSQL(PK p,IDialect tarDia){
		StringBuffer pkSQL = new StringBuffer("alter table ");
		String pk = p.getName();
		if(pk.length()>30){
		    pk = "pk_"+StringUtils.substring(p.getPkTableName(),0, 10) +"_"+ RandomStringUtils.random(8,s);
		}
		pkSQL.append(p.getPkTableName()).append(" add constraint ").append(pk).append(" primary key(").append(tarDia.getNamingChar()).append(p.getPkTableColumn().replace(",", tarDia.getNamingChar()+","+tarDia.getNamingChar())).append(tarDia.getNamingChar()).append(")");
		return pkSQL.toString();
	}

	/**
	 * 构建添加外键SQL
	 * @param f 外键信息
	 * @param tarDia 目标库方言
	 * @return
	 */
	//alter table DM_RIGHT add constraint FK_DM_RIGHT__CFG_SUBMODULE     foreign key(MODULE_ID) references CFG_SUBMODULE(ID) ON DELETE  CASCADE;
	private static String buildAddFKSQL(FK f,IDialect tarDia){
		StringBuffer fkSQL = new StringBuffer("alter table ");
		String fk = f.getName();
		if(fk.length() > 30){
		    fk = "fk_"+StringUtils.substring(f.getName(),0, 10) +"_"+ RandomStringUtils.random(8,s);
		}
		fkSQL.append(tarDia.getNamingChar()).append(f.getFkTableName()).append(tarDia.getNamingChar()).append(" add constraint ").append(fk)
		.append(" foreign key(").append(tarDia.getNamingChar()).append(f.getFkTableColumn()).append(tarDia.getNamingChar()).append(")")
		.append(" references ").append(tarDia.getNamingChar()).append(f.getPkTableName()).append(tarDia.getNamingChar()).append("(").append(tarDia.getNamingChar()).append(f.getPkTableColumn()).append(tarDia.getNamingChar()).append(")");
		if(f.getDeleteRule()!=null){
			fkSQL.append(f.getDeleteRule());
		}
		return fkSQL.toString();
	}
	
	/**
	 * 构建建表语句
	 * @param tm 表结构
	 * @param tarDia 目标库方言
	 * @return
	 * @throws SQLException 
	 */
	private static String buildCreateTableSQL(TableMeta tm,IDialect tarDia) throws SQLException{
		StringBuffer createSQL = new StringBuffer("create table ");
		createSQL.append(tarDia.getNamingChar()).append(tm.getName()).append(tarDia.getNamingChar()).append("(");
		//生成列
		List<ColumnMeta> cols = tm.getColumnMetaLs();
		
		if(tarDia.maxCols()>0 && cols.size()>tarDia.maxCols()){
		    System.err.println("目标表的列：\n");
		    for (int i = 0; i < cols.size(); i++) {
		        System.err.println(cols.get(i).getColumnName());
            }
			throw new SQLException("目标库不支持单表超过 "+tarDia.maxCols()+" 的列，计划生成的表格["+tm.getName()+"],有"+cols.size()+" 列");
		}
		for (int i = 0; i < cols.size(); i++) {
			ColumnMeta cm = cols.get(i);
			createSQL.append(tarDia.getNamingChar()).append(cm.getColumnName()).append(tarDia.getNamingChar()).append(" ").append(tarDia.javaType2SQLType(cm.getJavaType()));
			if(cm.getLength()>0 && tarDia.columnTypeHasLength(cm,cm.getJavaType())){
				createSQL.append("(").append(tarDia.getLength(cm.getJavaType(),cm.getLength()));
				if(cm.getDecimal()!=null && tarDia.columnTypeHasDecimal(cm.getJavaType()) && cm.getDecimal()>0){
					createSQL.append(",").append(cm.getDecimal());
				}
				createSQL.append(")");
			}
			if(!cm.isNullAble()){
				createSQL.append(" NOT NULL ");
			}
			if(i < cols.size()-1){
				createSQL.append(",");
			}
		}
		createSQL.append(")");
		return createSQL.toString();
	}

	/**
	 * 获取表数据
	 * @param srcConn 数据源库连接
	 * @param srcTM 数据源表结构
	 * @param srcDia 数据源库方言
	 * @return
	 * @throws SQLException
	 */
	public static List<Map<String,Object>> getTableData(Connection srcConn,TableMeta srcTM, IDialect srcDia,String where) throws SQLException{
		List<Map<String,Object>> values =new ArrayList<Map<String,Object>>();
		//查询SQL
		String sql = "select * from "+srcDia.getNamingChar()+srcTM.getName()+srcDia.getNamingChar();
		if(where != null){
			sql += where;
		}
		ResultSet rs = srcConn.createStatement().executeQuery(sql);
		while(rs.next()){
			List<ColumnMeta> cmLs = srcTM.getColumnMetaLs();
			Map<String,Object> data = new HashMap<String, Object>();
			for (int i = 0; i < cmLs.size(); i++) {
				ColumnMeta cm = cmLs.get(i);
				String colName = cm.getColumnName();
				Object val = null;
				if(Types.CLOB==cm.getJavaType()){
					val = rs.getString(colName);
				}else{
					try {
                        val = rs.getObject(colName);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
				}
//				switch(cm.getJavaType()){
//					case Types.CHAR: 
//					case Types.CLOB:
//					case Types.VARCHAR: val = rs.getString(colName); break;
//					case Types.DATE: val = rs.getDate(colName); break;
//					case Types.TIMESTAMP: val = rs.getTimestamp(colName); break;
//					case Types.NUMERIC: 
//					case Types.DECIMAL: val = rs.getBigDecimal(colName); break;
//					case Types.INTEGER: val = rs.getObject(colName)!=null?rs.getInt(colName):null; break;
//					case Types.VARBINARY:
//					case Types.BLOB:
//					default: val = rs.getObject(colName); break;
//				}
				data.put(colName, val);
			}
			values.add(data);
		}
		return values;
	}
	
	/**
	 * 比较数据，源：比较的标准，目标：需要向标准对齐
	 * @param srcInfo
	 * @param tarInfo
	 * @param osw
	 * @throws SQLException
	 * @throws IOException
	 */
	public static void compareData(DBInfo srcInfo,DBInfo tarInfo,String[] tables,OutputStreamWriter osw,boolean needCloseOS) throws SQLException, IOException{
		List<TableMeta> sMls = srcInfo.getMeta().getTableMetas(true,true,true,tables);
		List<TableMeta> tMls = tarInfo.getMeta().getTableMetas(true,true,true,tables);
		System.out.println("[准备信息]：来源库有 "+sMls.size()+"个表");
		System.out.println("[准备信息]：目标库有 "+tMls.size()+"个表");
		osw.write("\n\n--[准备信息]：来源库有 "+sMls.size()+"个表\n");
		osw.write("\n\n--[准备信息]：目标库有 "+tMls.size()+"个表\n");
		for (TableMeta srcTm : sMls) {
			System.out.println("比对表： "+srcTm.getName()+" ...");
			TableMeta tarTm = null;
			for (TableMeta tTm : tMls) {
				if(srcTm.getName().equalsIgnoreCase(tTm.getName())){
					tarTm = tTm;
					break;
				}
			}
			if(tarTm==null){
				System.err.println("无法对比表["+srcTm.getName()+"],其在目标库中不存在");
			}else{
				List<Map<String,Object>> srcRows = getTableData(srcInfo.getConn(), srcTm, srcInfo.getDialect(), null);
				List<Map<String,Object>> tarRows = getTableData(tarInfo.getConn(), tarTm, tarInfo.getDialect(), null);
				List<Map<String,Object>> addRows = new ArrayList<Map<String,Object>>();
				for (Map<String, Object> sRow : srcRows) {
					Object srcPkVal = sRow.get(srcTm.getPKname());
					Map<String, Object> tRow = null;
					for (Map<String, Object> tR : tarRows) {
						Object tarPkVal = tR.get(tarTm.getPKname());
						if(srcPkVal.equals(tarPkVal)){
							tRow = tR;
							break;
						}
					}
					if(tRow==null){//情况1 目标需要新增数据
						addRows.add(sRow);
					}else{
						//TODO 情况2 需要更新的数据
					}
				}
				if(addRows.size()>0){
					List<String> insertLs = buildInsertSQL(addRows, tarTm, tarInfo.getDialect());
					osw.write("\n\n--[补充记录]：表["+tarTm.getName()+"]的"+insertLs.size()+"条记录\n");
					for (String insertSQL : insertLs) {
						osw.write(insertSQL);
						osw.write(";\n");
					}
				}
				//TODO 情况3 需要删除的数据
				List<String> delRows = new ArrayList<String>();
				for (Map<String, Object> rRow : tarRows) {
					Object tarPkVal = rRow.get(tarTm.getPKname());
					Map<String, Object> sRow = null;
					for (Map<String, Object> sR : srcRows) {
						Object srcPkVal = sR.get(srcTm.getPKname());
						if(srcPkVal.equals(tarPkVal)){
							sRow = sR;
							break;
						}
					}
					if(sRow==null){//需要删除的数据
//						char srcNc = srcInfo.getDialect().getNamingChar();
						String tarNc = tarInfo.getDialect().getNamingChar();
						String delWhere = new StringBuffer(" where ").append(tarNc).append(tarTm.getPKname()).append(tarNc).append("='").append(tarPkVal).append("'").toString();
						String delSql = buildDeleteTableDataSQL(tarTm, tarInfo.getDialect(), delWhere);
						delRows.add(delSql);
					}
				}
				if(delRows.size()>0){
					osw.write("\n\n--[删除记录]：表["+tarTm.getName()+"]的"+delRows.size()+"条记录\n");
					for (String delSQL : delRows) {
						osw.write(delSQL);
						osw.write(";\n");
					}
				}
			}
		}
		if(needCloseOS){
			osw.flush();
			osw.close();
		}
	}
	
	/**
	 * 对比数据库，源：比较的标准，目标：需要向标准对齐
	 * 即生成的sql为参照源表来修改目标表的sql
	 * @param srcDia
	 * @param targetDia
	 * @throws SQLException 
	 * @throws IOException 
	 *///TODO 缺少主外键 和索引
	public static void compareDB(DBInfo srcInfo,DBInfo tarInfo,OutputStreamWriter osw,boolean needCloseOS) throws SQLException, IOException{
		List<TableMeta> sMls = srcInfo.getMeta().getTableMetas(true,true,true,null);
		List<TableMeta> tMls = tarInfo.getMeta().getTableMetas(true,true,true,null);
		osw.write("\n\n--[准备信息]：来源库有 "+sMls.size()+"个表\n");
		osw.write("\n\n--[准备信息]：目标库有 "+tMls.size()+"个表\n");
		for (TableMeta srcTm : sMls) {
			TableMeta tarTm = null;
			for (TableMeta tTm : tMls) {
				if(srcTm.getName().equalsIgnoreCase(tTm.getName())){
					tarTm = tTm;
					//TODO 增加待删除的表
					break;
				}
			}
			if(tarTm==null){//情况1,没有表，需要新增
				//1.1.在目标库创建表
				String createTableSql = buildCreateTableSQL(srcTm, tarInfo.getDialect());
				osw.write("\n\n--表["+srcTm.getName()+"]需要创建\n");
				try {
					osw.write(createTableSql);
					osw.write(";\n");
				} catch (Exception e) {
					System.err.println("[建表语句]："+createTableSql);
					e.printStackTrace();
				}
				/*//4.获取源数据
				List<Map<String, Object>> datas = getTableData(srcConn, srcTm, srcDia,null);
				//5.向目标库插入数据
				List<String> insertSQLs = buildInsertSQL(datas, srcTm, tarDia);
				osw.write("表["+srcTm.getName()+"]需要增加数据 "+insertSQLs.size()+" 行");
				for (int j = 0; j < insertSQLs.size(); j++) {
					try {
						osw.write(insertSQLs.get(j));
					} catch (Exception e) {
						osw.write("[插入语句]："+insertSQLs.get(j));
						e.printStackTrace();
					}
				}*/
			}else{//情况2,有表，需要比较字段 和 对比数据
				//2.1得到比源表少的字段来补充
				List<CompColumnMeta> cms = compareColumn(srcTm,tarTm);
				CompUtils.subColumns(cms, CompColumnMeta.TYPE_ADDCOLUMN);
				if(cms.size()>0){
					//2.1.1 生成需要新增的字段
					List<ColumnMeta> addColumnLs = CompUtils.subColumns(cms, CompColumnMeta.TYPE_ADDCOLUMN); 
					if(addColumnLs.size()>0){
						List<String> addColumnSQLs = buildAddColumnSQL(addColumnLs, tarInfo.getDialect());
						osw.write("\n\n--表["+tarTm.getName()+"]需要补充 "+addColumnSQLs.size()+" 列\n");
						for (int j = 0; j < addColumnSQLs.size(); j++) {
							try {
								osw.write(addColumnSQLs.get(j));
								osw.write(";\n");
							} catch (Exception e) {
								System.err.println("[补充列语句]："+addColumnSQLs.get(j));
								e.printStackTrace();
							}
						}
					}
					//2.1.2 生产需要删除的字段
					List<ColumnMeta> delColumnLs = CompUtils.subColumns(cms, CompColumnMeta.TYPE_DELCOLUMN); 
					if(addColumnLs.size()>0){
						List<String> delColumnSQLs = buildDelColumnSQL(delColumnLs, tarInfo.getDialect());
						osw.write("\n\n--表["+tarTm.getName()+"]需要删除 "+delColumnSQLs.size()+" 列\n");
						for (int j = 0; j < delColumnSQLs.size(); j++) {
							try {
								osw.write(delColumnSQLs.get(j));
								osw.write(";\n");
							} catch (Exception e) {
								System.err.println("[补充列语句]："+delColumnSQLs.get(j));
								e.printStackTrace();
							}
						}
					}
					//2.1.3 生成需要修改的字段（类型不同或长度不同）
					List<ColumnMeta> modifyColumnLs = CompUtils.subColumns(cms, CompColumnMeta.TYPE_DIFFTYPE); 
					modifyColumnLs.addAll(CompUtils.subColumns(cms, CompColumnMeta.TYPE_DIFFLENGTH)); 
					if(modifyColumnLs.size()>0){
						List<String> modifyColumnSQLs = buildModifyColumnSQL(modifyColumnLs, tarInfo.getDialect());
						osw.write("\n\n--表["+tarTm.getName()+"]需要修改类型或长度的 "+modifyColumnSQLs.size()+" 列\n");
						for (int j = 0; j < modifyColumnSQLs.size(); j++) {
							try {
								osw.write(modifyColumnSQLs.get(j));
								osw.write(";\n");
							} catch (Exception e) {
								System.err.println("[补充列语句]："+modifyColumnSQLs.get(j));
								e.printStackTrace();
							}
						}
					}
					
				}
			}
		}
		if(needCloseOS){
			osw.flush();
			osw.close();
		}
	}
	
	//为clob列提供多次update进行数据插入。
	private static final String ADD_COLUMN_SQL="alter table [tablename] add [colName] [type][length]";
	/**
	 * 生成补充列的sql
	 * @param cms
	 * @param tarDia
	 * @return
	 */
	private static List<String> buildAddColumnSQL(List<ColumnMeta> cms,IDialect tarDia) {
		List<String> addColumnSQLs = new ArrayList<String>();
		for (ColumnMeta cm : cms) {
			StringBuffer length = new StringBuffer(); 
			if(cm.getLength()>0 && tarDia.columnTypeHasLength(cm,cm.getJavaType())){
				length.append("(").append(cm.getLength());
				if(cm.getDecimal()!=null && tarDia.columnTypeHasDecimal(cm.getJavaType())){
					length.append(",").append(cm.getDecimal());
				}
				length.append(")");
			}
			String addColSQL = ADD_COLUMN_SQL.replace("[tablename]", tarDia.getNamingChar()+cm.getTableName()+tarDia.getNamingChar())
			.replace("[colName]", tarDia.getNamingChar()+cm.getColumnName()+tarDia.getNamingChar())
			.replace("[type]", tarDia.javaType2SQLType(cm.getJavaType()))
			.replace("[length]", length.toString());
			addColumnSQLs.add(addColSQL);
		}
		return addColumnSQLs;
	}

	//为clob列提供多次update进行数据插入。
	private static final String DEL_COLUMN_SQL="alter table [tablename] drop column [colName] ";
	/**
	 * 生成补充列的sql
	 * @param cms
	 * @param tarDia
	 * @return
	 */
	private static List<String> buildDelColumnSQL(List<ColumnMeta> cms,IDialect tarDia) {
		List<String> delColumnSQLs = new ArrayList<String>();
		for (ColumnMeta cm : cms) {
			String addColSQL = DEL_COLUMN_SQL.replace("[tablename]", tarDia.getNamingChar()+cm.getTableName()+tarDia.getNamingChar())
			.replace("[colName]", tarDia.getNamingChar()+cm.getColumnName()+tarDia.getNamingChar());
			delColumnSQLs.add(addColSQL);
		}
		return delColumnSQLs;
	}
	
	private static final String MODIFY_COLUMN_SQL="alter table [tablename] [modify] [colName] [type][length]";
	/**
	 * 生成修改列的sql
	 * @param cms
	 * @param tarDia
	 * @return
	 */
	private static List<String> buildModifyColumnSQL(List<ColumnMeta> cms,IDialect tarDia) {
		List<String> modifyColumnSQLs = new ArrayList<String>();
		for (ColumnMeta cm : cms) {
			StringBuffer length = new StringBuffer(); 
			if(cm.getLength()>0 && tarDia.columnTypeHasLength(cm,cm.getJavaType())){
				length.append("(").append(cm.getLength());
				if(cm.getDecimal()!=null && tarDia.columnTypeHasDecimal(cm.getJavaType())){
					length.append(",").append(cm.getDecimal());
				}
				length.append(")");
			}
			String sql = MODIFY_COLUMN_SQL.replace("[tablename]", tarDia.getNamingChar()+cm.getTableName()+tarDia.getNamingChar())
					.replace("[modify]", tarDia.getModifyColumnKey())
					.replace("[colName]", tarDia.getNamingChar()+cm.getColumnName()+tarDia.getNamingChar())
					.replace("[type]", tarDia.javaType2SQLType(cm.getJavaType()))
					.replace("[length]", length.toString());
			modifyColumnSQLs.add(sql);
		}
		return modifyColumnSQLs;
	}
	/**
	 * 对比字段，返回a表比b表(a是标准，b是要修改的表)
	 * 	1、多的字段
	 *  2、不一致的类型
	 *  3、不一致的长度
	 * @param aTm
	 * @param bTm
	 * @return
	 */
	private static List<CompColumnMeta> compareColumn(TableMeta aTm,TableMeta bTm) {
		List<ColumnMeta> aCmLs = aTm.getColumnMetaLs();
		List<ColumnMeta> bCmLs = bTm.getColumnMetaLs();
		List<CompColumnMeta> diffCms = new ArrayList<CompColumnMeta>();
		for (ColumnMeta aCm : aCmLs) {
			ColumnMeta bCm = null;
			for (ColumnMeta bCmTmp : bCmLs) {
				if(bCmTmp.getColumnName().equalsIgnoreCase(aCm.getColumnName())){
					bCm=bCmTmp;
					break;
				}
			}
			if(bCm==null){//1、需要补充的字段
				diffCms.add(new CompColumnMeta(aCm,CompColumnMeta.TYPE_ADDCOLUMN));
			}else{
				if(aCm.getJavaType()!=bCm.getJavaType()){//2、需要调整类型的字段
					diffCms.add(new CompColumnMeta(aCm,CompColumnMeta.TYPE_DIFFTYPE));
				}else if(aCm.getLength()!=bCm.getLength()){//3、需要调整长度的字段
					diffCms.add(new CompColumnMeta(aCm,CompColumnMeta.TYPE_DIFFLENGTH));
				}
			}
		}
		//4、要删除的字段
		for (ColumnMeta bCm : bCmLs) {
			boolean hasColumn = false;
			for (ColumnMeta aCm : aCmLs) {
				if(aCm.getColumnName().equalsIgnoreCase(bCm.getColumnName())){
					hasColumn=true;
					break;
				}
			}
			if(!hasColumn){
				diffCms.add(new CompColumnMeta(bCm,CompColumnMeta.TYPE_DELCOLUMN));
			}
		}
		return diffCms;
	}


	/**
     * 构建目标库插入语句update
     * @param values 要插入的数据
     * @param targetTM 目标库表结构
     * @param targetDia 目标库方言
     * @return
     * @throws SQLException 
     */
    private static List<String> buildUpdateSQL(List<Map<String,Object>> values, TableMeta targetTM, IDialect targetDia) throws SQLException{
        List<String> updateSQLs = new ArrayList<String>();
        List<String> colbSQLs = new ArrayList<String>();
        //生成列
        List<ColumnMeta> cols = targetTM.getColumnMetaLs();
        String pkName = targetTM.getPKname();
        for (int j = 0; j < values.size(); j++) {
            StringBuffer updateSQL = new StringBuffer("update ");
            updateSQL.append(targetDia.getNamingChar()).append(targetTM.getName()).append(targetDia.getNamingChar()).append(" set ");
            Map<String,Object> val = values.get(j);
            String pkVal = null;
            for (int i = 0; i < cols.size(); i++) {
                ColumnMeta cm = cols.get(i);
                Object v = val.get(cm.getColumnName());
                if(pkName.equals(cm.getColumnName())){
                    pkVal = targetDia.processSQLVal(v, cm.getJavaType(), cm);
                    continue;
                }
                String vStr = "NULL";
                if(Types.CLOB == cm.getJavaType() || Types.LONGVARCHAR == cm.getJavaType()){//clob使用update处理
                    if(v!=null && v.toString().length()>0){
                        vStr = "''";
                        colbSQLs.addAll(targetDia.buildClobSql(targetTM, cm.getColumnName(), val));
                    }
                }else{
                    vStr = targetDia.processSQLVal(v, cm.getJavaType(), cm);
                }
    //          if(Types.CLOB == cm.getJavaType())continue;//clob通过update来赋值
                updateSQL.append(targetDia.getNamingChar()).append(cm.getColumnName()).append(targetDia.getNamingChar()).append("=").append(vStr);
                if(i < cols.size()-1){
                    updateSQL.append(",");
                }
            }
            if(pkVal != null){
                updateSQL.append(" where ").append(pkName).append(" = ").append(pkVal);
                updateSQLs.add(updateSQL.toString());
            }
        }
        if(colbSQLs.size()>0){
            updateSQLs.addAll(colbSQLs);
        }
        return updateSQLs;
    }

	/**
	 * 构建目标库插入语句insert
	 * @param values 要插入的数据
	 * @param targetTM 目标库表结构
	 * @param targetDia 目标库方言
	 * @return
	 * @throws SQLException 
	 */
	public static List<String> buildInsertSQL(List<Map<String,Object>> values, TableMeta targetTM, IDialect targetDia) throws SQLException{
		List<String> insertSQLs = new ArrayList<String>();
		List<String> colbSQLs = new ArrayList<String>();
		StringBuffer insertSQL = new StringBuffer("insert into ");
		insertSQL.append(targetDia.getNamingChar()).append(targetTM.getName()).append(targetDia.getNamingChar());
		//生成列
		List<ColumnMeta> cols = targetTM.getColumnMetaLs();
		StringBuffer colNames = new StringBuffer();
		for (int i = 0; i < cols.size(); i++) {
			ColumnMeta cm = cols.get(i);
//			if(Types.CLOB == cm.getJavaType())continue;//clob通过update来赋值
			colNames.append(targetDia.getNamingChar()).append(cm.getColumnName()).append(targetDia.getNamingChar());
			if(i < cols.size()-1){
				colNames.append(",");
			}
		}
//		if(','==colNames.charAt(colNames.length()-1)){
//			colNames.deleteCharAt(colNames.length()-1);
//		}
		insertSQL.append("(").append(colNames).append(")").append(" values(^{values})");
		for (int j = 0; j < values.size(); j++) {
			Map<String,Object> val = values.get(j);
			StringBuffer colValues = new StringBuffer();
			for (int i = 0; i < cols.size(); i++) {
				ColumnMeta cm = cols.get(i);
				Object v = val.get(cm.getColumnName());
				if(Types.CLOB == cm.getJavaType() || Types.LONGVARCHAR == cm.getJavaType()){//clob使用update处理
					if(v!=null && v.toString().length()>0){
						colValues.append("''");
						colbSQLs.addAll(targetDia.buildClobSql(targetTM, cm.getColumnName(), val));
					}else{
						colValues.append("NULL");
					}
				}else{
					colValues.append(targetDia.processSQLVal(v, cm.getJavaType(), cm));
				}
				if(i < cols.size()-1){
					colValues.append(",");
				}
			}
//			if(','==colValues.charAt(colValues.length()-1)){
//				colValues.deleteCharAt(colValues.length()-1);
//			}
			insertSQLs.add(insertSQL.toString().replace("^{values}", colValues.toString()));
		}
		if(colbSQLs.size()>0){
			insertSQLs.addAll(colbSQLs);
		}
		return insertSQLs;
	}
	

    /**
     * 构建目标库插入语句insert
     * 不支持colb内容导出
     * @param values 要插入的数据
     * @param targetTM 目标库表结构
     * @param targetDia 目标库方言
     * @return
     * @throws SQLException 
     */
    public static List<String> buildInsertSQL(String tableName, ResultSet rs, IDialect targetDia) throws SQLException{
        ResultSetMetaData rsm = rs.getMetaData();
        List<String> insertSQLs = new ArrayList<String>();
        List<String> colbSQLs = new ArrayList<String>();
        StringBuffer insertSQL = new StringBuffer("insert into ");
        insertSQL.append(targetDia.getNamingChar()).append(tableName).append(targetDia.getNamingChar());
        //生成列
        StringBuffer colNames = new StringBuffer();
        for (int i = 0; i < rsm.getColumnCount(); i++) {
//          if(Types.CLOB == cm.getJavaType())continue;//clob通过update来赋值
            colNames.append(targetDia.getNamingChar()).append(rsm.getColumnLabel(i + 1)).append(targetDia.getNamingChar());
            if(i < rsm.getColumnCount()-1){
                colNames.append(",");
            }
        }
//      if(','==colNames.charAt(colNames.length()-1)){
//          colNames.deleteCharAt(colNames.length()-1);
//      }
        insertSQL.append("(").append(colNames).append(")").append(" values(^{values})");
        while (rs.next()) {
            StringBuffer colValues = new StringBuffer();
            for (int i = 0; i < rsm.getColumnCount(); i++) {
                Object v = rs.getObject(i + 1);
                int javaType = rsm.getColumnType(i + 1);
                if(Types.CLOB == javaType || Types.LONGVARCHAR == javaType){//clob使用update处理
//                    if(v!=null && v.toString().length()>0){
//                        colValues.append("''");
//                        colbSQLs.addAll(targetDia.buildClobSql(targetTM, cm.getColumnName(), val));
//                    }else{
                        colValues.append("NULL");
//                    }
                }else{
                    colValues.append(targetDia.processSQLVal(v, javaType, null));
                }
                if(i < rsm.getColumnCount()-1){
                    colValues.append(",");
                }
            }
//          if(','==colValues.charAt(colValues.length()-1)){
//              colValues.deleteCharAt(colValues.length()-1);
//          }
            insertSQLs.add(insertSQL.toString().replace("^{values}", colValues.toString()));
        }
        if(colbSQLs.size()>0){
            insertSQLs.addAll(colbSQLs);
        }
        return insertSQLs;
    }
}
